package club.jdiy.core.sql;

import club.jdiy.core.AppContext;
import club.jdiy.core.base.domain.Pager;
import club.jdiy.core.properties.JDiyProperties;
import club.jdiy.core.ex.JDiyException;
import club.jdiy.core.ex.JDiySqlException;
import club.jdiy.utils.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.internal.SessionFactoryImpl;
import org.hibernate.persister.entity.AbstractEntityPersister;
import org.hibernate.persister.entity.EntityPersister;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Table;
import javax.persistence.metamodel.IdentifiableType;
import java.io.Reader;
import java.io.StringReader;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

/**
 * @author ziquee(子秋) QQ号:39886616 主页: http://www.jdiy.club
 * @version 20200125
 */
@Slf4j
@Component
public class SqlDao implements Dao {
    /**
     * 获取所有数据表信息
     *
     * @return 数据表信息
     */
    @Override
    public List<CommentInfo> getTables() {
        List<CommentInfo> ret = new ArrayList<>();
        getSession().doWork(cn -> {
            String sql = "select * from information_schema.TABLES where table_schema=? order by TABLE_NAME";
            try (PreparedStatement ps = cn.prepareStatement(sql)) {
                if (jDiyProperties.isShowsql()) log.info(sql);
                ps.setString(1, cn.getCatalog());
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    ret.add(new CommentInfo(
                            rs.getString("TABLE_NAME"),
                            rs.getString("TABLE_COMMENT")
                    ));
                }
            } catch (SQLException e) {
                throw new JDiySqlException(e.getMessage(), e.getCause());
            }
        });
        return ret;
    }

    @Override
    public TableInfo getTableInfo(String table) {
        TableInfo ret = context.getCacheClient().get("JDiyTableMetaInfo_" + table);
        if (ret == null) {
            AtomicReference<String> primaryKey = new AtomicReference<>("");
            final LinkedHashMap<String, ColumnInfo> columns = new LinkedHashMap<>();

            AtomicReference<Boolean> isView = new AtomicReference<>();
            getSession().doWork(cn -> {
                //select * from information_schema.`TABLES` t where t.TABLE_SCHEMA='amz'
                String sql = "select * from information_schema.TABLES t where t.TABLE_SCHEMA=? and t.TABLE_NAME=?";
                try (PreparedStatement ps = cn.prepareStatement(sql)) {
                    if (jDiyProperties.isShowsql()) log.info(sql);
                    ps.setString(1, cn.getCatalog());
                    ps.setString(2, table);
                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        isView.set((rs.getString("TABLE_TYPE") + "").contains("VIEW"));
                        break;
                    }
                } catch (SQLException e) {
                    throw new JDiySqlException(e.getMessage(), e.getCause());
                }
            });


            getSession().doWork(cn -> {
                String sql = "select c.*,k.REFERENCED_TABLE_NAME,k.REFERENCED_COLUMN_NAME" +
                        " from information_schema.COLUMNS c " +
                        " left join information_schema.KEY_COLUMN_USAGE k on k.TABLE_SCHEMA = c.TABLE_SCHEMA and k.TABLE_NAME=c.TABLE_NAME and k.COLUMN_NAME=c.COLUMN_NAME" +
                        " where c.TABLE_SCHEMA=? and c.TABLE_NAME=?";
                try (PreparedStatement ps = cn.prepareStatement(sql)) {
                    if (jDiyProperties.isShowsql()) log.info(sql);
                    ps.setString(1, cn.getCatalog());
                    ps.setString(2, table);
                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        String fieldName = rs.getString("COLUMN_NAME");
                        boolean isPrimaryKey = "PRI".equals(rs.getString("COLUMN_KEY"));
                        if (isPrimaryKey) primaryKey.set(fieldName);

                        columns.put(fieldName, new ColumnInfo(
                                fieldName,
                                rs.getString("COLUMN_COMMENT"),
                                rs.getString("DATA_TYPE"),
                                rs.getString("COLUMN_TYPE"),
                                isPrimaryKey,
                                "YES".equals(rs.getString("IS_NULLABLE")),
                                rs.getString("COLUMN_DEFAULT"),
                                rs.getString("REFERENCED_TABLE_NAME"),
                                rs.getString("REFERENCED_COLUMN_NAME"),
                                rs.getString("EXTRA")
                        ));
                    }
                } catch (SQLException e) {
                    throw new JDiySqlException(e.getMessage(), e.getCause());
                }
            });
            context.getCacheClient().set("JDiyTableMetaInfo_" + table, ret = new TableInfo(table, primaryKey.get(), columns, Boolean.TRUE.equals(isView.get())), 60 * 3);
        }
        return ret;
    }

    private String getEntityTableName(IdentifiableType<?> et) {
        Table t = et.getJavaType().getAnnotation(Table.class);
        if (t != null) return t.name();
        return et.getSupertype() == null ? null : getEntityTableName(et.getSupertype());
    }

    /**
     * 获取全部实体信息
     *
     * @return EntityMeta
     */
    @Override
    public List<EntityInfo> getEntityMeta() {
        Map<String, String> comments = getTables().stream()
                .collect(Collectors.toMap(CommentInfo::getName, CommentInfo::getComment, (a, b) -> b));

        EntityManagerFactory entityManagerFactory = em.getEntityManagerFactory();
        SessionFactoryImpl sessionFactory = (SessionFactoryImpl) entityManagerFactory.unwrap(SessionFactory.class)
                .getMetamodel().getSessionFactory();

        return sessionFactory.getMetamodel().entityPersisters().values().stream().map(entityPersister -> {
            AbstractEntityPersister ap = (AbstractEntityPersister) entityPersister;
            Class<?> targetClass = ap.getMappedClass();
            String comment = comments.get(ap.getTableName());
            return new EntityInfo(
                    targetClass.getPackage().getName(),
                    targetClass.getSimpleName(),
                    ap.getTableName(),
                    comment);
        }).sorted(Comparator.comparing(EntityInfo::getName)
        ).collect(Collectors.toList());
    }

    /**
     * 获取某个实体的Meta信息
     *
     * @return EntityMeta
     */
    @Override
    public EntityInfo getEntityMeta(String entity) {
        EntityManagerFactory entityManagerFactory = em.getEntityManagerFactory();
        SessionFactoryImpl sessionFactory = (SessionFactoryImpl) entityManagerFactory.unwrap(SessionFactory.class).getMetamodel().getSessionFactory();
        AbstractEntityPersister ap = (AbstractEntityPersister) sessionFactory.getMetamodel().entityPersister(entity);
        Class<?> targetClass = ap.getMappedClass();

        String comment = getTables().stream()
                .filter(tm -> tm.getName().equals(ap.getTableName())).findFirst().map(CommentInfo::getComment)
                .orElse("");

        EntityInfo e = new EntityInfo(
                targetClass.getPackage().getName(),
                targetClass.getSimpleName(),
                ap.getTableName(),
                comment);

        Map<String, ColumnInfo> columnMetaMap = this.getTableInfo(ap.getTableName()).getColumns();

        List<EntityFieldInfo> ls = new ArrayList<>();
        if (ap.hasIdentifierProperty()) {
            EntityFieldInfo ef = new EntityFieldInfo(ap.getIdentifierPropertyName(), ap.getIdentifierType().getReturnedClass());
            ef.setColumn(ap.getIdentifierColumnNames()[0]);

            ColumnInfo idMeta = columnMetaMap.get(ap.getIdentifierColumnNames()[0]);
            if (idMeta != null) {
                ef.setColumnType(idMeta.getType());
                ef.setComment(idMeta.getComment());
            }
            ls.add(ef);
        }
        ap.getAttributes().forEach(attr ->
                Optional.ofNullable(BeanUtils.getPropertyDescriptor(targetClass, attr.getName())).ifPresent(targetPd -> {
                    EntityFieldInfo ef = new EntityFieldInfo(attr.getName(), targetPd.getPropertyType());

                    if (Collection.class.isAssignableFrom(targetPd.getPropertyType())) {
                        ef.setType(EntityFieldInfo.Type.LIST);
                        try {
                            ef.setListClass((Class<?>) ((ParameterizedType) targetPd.getReadMethod().getGenericReturnType()).getActualTypeArguments()[0]);
                        } catch (Exception ignore) {
                        }
                    } else {
                        ef.setColumn(ap.getPropertyColumnNames(attr.getName())[0]);
                        ColumnInfo cm = columnMetaMap.get(ef.getColumn());
                        if (cm != null) {
                            ef.setColumnType(cm.getType());
                            ef.setComment(cm.getComment());
                        }

                        try {
                            EntityPersister ep = sessionFactory.getMetamodel().entityPersister(targetPd.getPropertyType());
                            if (ep == null) {
                                ef.setType(EntityFieldInfo.Type.BASIC);
                            } else {
                                ef.setType(EntityFieldInfo.Type.JOIN);
                                ef.setMemberIdClass(ep.getIdentifierType().getReturnedClass());
                            }
                        } catch (Exception ignore) {
                        }
                    }
                    ls.add(ef);
                }));
        e.setFieldList(ls);
        return e;
    }

    /**
     * 根据数据表创建一个新的Rs对象
     *
     * @param table 数据表名(必须存在)
     * @return rs
     */
    @Override
    public Rs create(String table) {
        Assert.notNull(table, "表名不能为空!");
        TableInfo tableInfo = getTableInfo(table);
        if (tableInfo.getColumns().isEmpty()) throw new JDiyException("目标数据表(" + table + ")不存在！");
        return new Rs(tableInfo);
    }

    @Override
    @Transactional
    public Rs rs(String table, String id) {
        return rs(table, id, "*");
    }

    @Override
    @Transactional
    public Rs rs(String table, Number id) {
        return rs(table, id, "*");
    }

    @Override
    @Transactional
    public Rs rs(String table, String id, String columns) {
        TableInfo tableInfo = getTableInfo(table);

        return StringUtils.isEmpty(id) || "0".equals(id)
                ? create(table)
                : rs(String.format("select %s from %s where %s='%s'",
                columns, table, tableInfo.getPrimaryKey(), id.replaceAll("'", "''")));
    }

    @Override
    @Transactional
    public Rs rs(String table, Number id, String columns) {
        TableInfo tableInfo = getTableInfo(table);
        return id == null || id.equals(0)
                ? create(table)
                : rs(String.format("select %s from %s where %s=%s", columns, table, tableInfo.getPrimaryKey(), id));
    }

    @Override
    @Transactional
    public Rs rs(String sql) {
        return ((LsImpl) ls(sql, 1, 1)).getOne();
    }

    @Override
    @Transactional
    public void exec(String sql, Object... pms) {
        getSession().doWork(cn -> {
            if (jDiyProperties.isShowsql()) log.info(sql);
            try (PreparedStatement ps = cn.prepareStatement(sql)) {
                int i = 0;
                for (Object p : pms) ps.setObject(++i, p);
                ps.executeUpdate();
            } catch (SQLException e) {
                throw new JDiySqlException(e.getMessage(), e.getCause());
            }
        });
    }


    //======================================================vvvv======================================================


    @Override
    @Transactional
    public Rs rs(Args pm) {
        int pageSize = pm.getPageSize();
        int absPage = pm.getAbsPage();
        pm.setPageSize(1);
        pm.setAbsPage(1); // 分页应当忽略，所以要这行
        Rs d = ((LsImpl) ls(pm, true)).getOne();
        pm.setPageSize(pageSize);
        pm.setAbsPage(absPage);
        return d;
    }


    private Session getSession() {
        return (Session) em.getDelegate();
    }


    @Override
    @Transactional
    public int exec(String sql) {
        AtomicInteger rtn = new AtomicInteger();
        getSession().doWork(cn -> {
            try (Statement smt = cn.createStatement()) {
                if (jDiyProperties.isShowsql()) log.info(sql);
                rtn.set(smt.executeUpdate(sql));
                if (jDiyProperties.isShowsql()) log.info(Locale.DBG.getString("II0015"), rtn);
            } catch (SQLException e) {
                throw new JDiySqlException(e.getMessage(), e.getCause());
            }
        });
        return rtn.get();
    }

    @Override
    @Transactional
    public int del(Rs d) {
        if (d.isNew()) {
            log.warn(Locale.DBG.getString("WN0003"));
            return 0;
        }
        if (d.getTable() == null) {
            throw new JDiySqlException(Locale.DBG.getString("EE0052"));
        }
        String primaryKey = d.getPrimaryKey();

        String sql = "DELETE FROM " + d.getTable() + " WHERE " + primaryKey + "='" + d.getString(primaryKey) + "'";
        int r = exec(sql);
        d.changedColumns.clear();
        d.isNew = true;
        return r;
    }

    @Override
    @Transactional
    public int del(Args args) {
        int cnt = 0;
        for (Rs d : ls(args).getItems()) cnt += del(d);
        return cnt;
    }


    @Override
    @Transactional
    public int len(Args args) {
        AtomicInteger count = new AtomicInteger();

        StringBuilder sql = new StringBuilder("SELECT COUNT(0) AS cnt FROM ").append(args.getTable());
        String f1 = args.getFilter();
        if (f1 != null && !(f1 = f1.trim()).equals("")) {
            String lower = f1.toLowerCase();
            int odb = lower.lastIndexOf("order by");
            if (odb != -1) f1 = f1.substring(0, odb);//取消order by增加速度.
            if ("".equals(f1) || lower.startsWith("group "))
                sql.append(f1);
            else if (!"1=1".equals(f1.trim()))
                sql.append(" WHERE ").append(f1);
        }
        getSession().doWork(cn -> {
            boolean hasErr = false;//复合查询可能会出错
            String sqlQuery = sql.toString();
            if (jDiyProperties.isShowsql()) log.info(sqlQuery);
            try (Statement smt = cn.createStatement(); ResultSet rs = smt.executeQuery(sqlQuery)) {
                if (rs.next()) count.set(rs.getInt(1));
            } catch (SQLException e) {
                hasErr = true;
            }
            if (hasErr) {
                int pmsize = args.getPageSize();
                args.setPageSize(0);
                count.set(ls(args).getItems().size());
                args.setPageSize(pmsize);
            }
        });
        return count.get();
    }

    @Override
    @Transactional
    public Pager<Rs> ls(Args args) {
        return ls(args, false);
    }

    @Override
    @Transactional
    public Pager<Rs> ls(String sql, int pageSize, int absPage) {
        if (jDiyProperties.isShowsql()) log.info(sql);
        AtomicReference<Pager<Rs>> ret = new AtomicReference<>(null);
        getSession()
                .doWork(cn -> {
                    try (Statement smt = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = smt.executeQuery(sql)) {
                        rs.last();
                        int rowCount = rs.getRow();

                        if (pageSize <= 0 || absPage <= 1) rs.beforeFirst();
                        else if (absPage <= (rowCount + pageSize - 1) / pageSize)
                            rs.absolute((absPage - 1) * pageSize);

                        TableInfo tableInfo = getTableInfo(getTableNameFromRSMD(rs.getMetaData()));
                        if (tableInfo.getColumns().size()<1) {
                            int b = sql.toUpperCase().indexOf(" FROM ");
                            if (b != -1 && sql.length() > 6) {
                                String tableName = sql.substring(b + 6);
                                b = tableName.indexOf(" ");
                                if (b != -1) tableName = tableName.substring(0, b);
                                tableInfo = getTableInfo(tableName);
                            }
                        }//如果rsmd取不到表名，才用此表名.

                        ret.set(initLs(tableInfo, rs, rowCount, pageSize, absPage));
                    } catch (SQLException e) {
                        throw new JDiySqlException(e.getMessage(), e.getCause());
                    }
                });
        return ret.get();
    }

    private Pager<Rs> ls(Args args, boolean isGetOne) {
        AtomicReference<Pager<Rs>> ret = new AtomicReference<>(null);
        getSession().doWork(cn -> {
            int rowCount = 0;
            if (args.getPageSize() > 0) {
                rowCount = isGetOne ? 1 : SqlDao.this.len(args.clone());
            }
            String filter = args.getFilter() != null && !args.getFilter().trim().equals("")
                    ? args.getFilter()
                    : null;
            int pageStart = Math.max((args.getAbsPage() - 1) * args.getPageSize(), 0);
            String sql = getPageBreakSql(args, filter, pageStart);
            if (jDiyProperties.isShowsql()) log.info(sql);
            try (Statement smt = cn.createStatement(); ResultSet rs = smt.executeQuery(sql)) {
                TableInfo tableInfo = getTableInfo(getTableNameFromRSMD(rs.getMetaData()));
                if (tableInfo.getColumns().size() < 1) {
                    String tableName = args.getTable().trim();
                    int pos = tableName.indexOf(",");
                    if (pos != -1) tableName = tableName.substring(0, pos);
                    pos = tableName.indexOf(" ");
                    if (pos != -1)
                        tableName = tableName.substring(0, pos);//use this,if 'rsmd' couldn't getConnection table name.
                    tableInfo = getTableInfo(tableName);
                }

                ret.set(SqlDao.this.initLs(tableInfo, rs, rowCount, args.getPageSize(), args.getAbsPage()));
            } catch (SQLException e) {
                throw new JDiySqlException(e.getMessage(), e.getCause());
            }
        });
        return ret.get();
    }


    @Override
    @Transactional
    public void save(Rs d) {
        getSession().doWork(cn -> {
            int upCount = 0;
            Set<ColumnInfo> needSaveFields = d.columnInfoMap.values().stream().filter(i ->
                    d.isNew ? d.containsKey(i.getName()) : d.changedColumns.contains(i.getName())
            ).collect(Collectors.toSet());


            if (!needSaveFields.isEmpty()) {
                if (d.isNew) {
                    char[] sa = new char[needSaveFields.size()];
                    Arrays.fill(sa, '?');
                    String s = Arrays.toString(sa);
                    s = s.substring(1, s.length() - 1);

                    List<String> fieldsArr = needSaveFields.stream().map(ColumnInfo::getName).collect(Collectors.toList());
                    String s1 = fieldsArr.toString();
                    s1 = s1.substring(1, s1.length() - 1);

                    String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", d.getTable(), s1, s);
                    if (jDiyProperties.isShowsql()) log.info(sql);

                    try (PreparedStatement psInsert = cn.prepareStatement(sql, new String[]{d.getPrimaryKey()})) {
                        this.setColumnValue(psInsert, d, needSaveFields);
                        upCount = psInsert.executeUpdate();
                        //get new id:
                        try (ResultSet rsgk = psInsert.getGeneratedKeys()) {
                            if (rsgk != null && rsgk.next()) {
                                d.set(d.getPrimaryKey(), rsgk.getObject(1));
                            }
                        }
                        d.changedColumns.clear();
                        d.isNew = false;
                    } catch (SQLException e) {
                        e.printStackTrace();
                        throw new JDiySqlException(e.getMessage(), e.getCause());
                    }
                } else {
                    StringBuilder sb = new StringBuilder("UPDATE ").append(d.getTable()).append(" SET ");
                    int i = 0;
                    for (ColumnInfo it : needSaveFields) {
                        if (i++ > 0) sb.append(", ");
                        sb.append(it.getName()).append("=?");
                    }
                    String sql = sb.append(" where ").append(d.getPrimaryKey()).append("=?").toString();
                    if (jDiyProperties.isShowsql()) log.info(sql);

                    try (PreparedStatement psUpdate = cn.prepareStatement(sql)) {
                        this.setColumnValue(psUpdate, d, needSaveFields);
                        psUpdate.setObject(needSaveFields.size() + 1, d.get(d.getPrimaryKey()));

                        upCount = psUpdate.executeUpdate();
                    } catch (SQLException e) {
                        throw new JDiySqlException(e.getMessage(), e.getCause());
                    }
                }
            }
            if (upCount > 0 && jDiyProperties.isShowsql()) log.info(Locale.DBG.getString("II0012"), upCount);
        });
    }

    private void setColumnValue(PreparedStatement ps, Rs rs, Set<ColumnInfo> needSaveFields) {
        int i = 0;
        try {
            for (ColumnInfo ci : needSaveFields) {
                if (ci.getType().startsWith("bit(")) {//fixed: bit(1)的 ci.getBaseType()会返回OTHER而不是BIT的bug
                    ps.setBoolean(++i, Boolean.TRUE.equals(rs.getBoolean(ci.getName())));
                    continue;
                }
                switch (ci.getBaseType()) {
                    case BIT:
                        ps.setBoolean(++i, Boolean.TRUE.equals(rs.getBoolean(ci.getName())));
                        break;
                    case TINYINT:
                        Byte b1 = rs.getByte(ci.getName());
                        if (b1 == null) ps.setNull(++i, Types.TINYINT);
                        else ps.setByte(++i, b1);
                        break;
                    case SMALLINT:
                        Short s1 = rs.getShort(ci.getName());
                        if (s1 == null) ps.setNull(++i, Types.SMALLINT);
                        else ps.setShort(++i, s1);
                        break;
                    case MEDIUMINT:
                    case INT:
                        Integer i1 = rs.getInt(ci.getName());
                        if (i1 == null) ps.setNull(++i, Types.INTEGER);
                        else ps.setInt(++i, i1);
                        break;
                    case BIGINT:
                        Long l1 = rs.getLong(ci.getName());
                        if (l1 == null) ps.setNull(++i, Types.BIGINT);
                        else ps.setLong(++i, l1);
                        break;
                    case FLOAT:
                        Float f1 = rs.getFloat(ci.getName());
                        if (f1 == null) ps.setNull(++i, Types.FLOAT);
                        else ps.setFloat(++i, f1);
                        break;
                    case DOUBLE:
                        Double d1 = rs.getDouble(ci.getName());
                        if (d1 == null) ps.setNull(++i, Types.DOUBLE);
                        else ps.setDouble(++i, d1);
                        break;
                    case DECIMAL:
                        BigDecimal bd = rs.getBigDecimal(ci.getName());
                        if (bd == null) ps.setNull(++i, Types.DECIMAL);
                        else ps.setBigDecimal(++i, bd);
                        break;
                    case CHAR:
                    case VARCHAR:
                    case TEXT:
                    case TINYTEXT:
                    case MEDIUMTEXT:
                    case LONGTEXT:
                        String s2 = rs.getString(ci.getName());
                        if (s2 == null) ps.setNull(++i, Types.VARCHAR);
                        else ps.setString(++i, s2);
                        break;
                    case CLOB:
                        String s3 = rs.getString(ci.getName());
                        if (s3 == null) ps.setNull(++i, Types.CLOB);
                        else ps.setCharacterStream(++i, new StringReader(s3), s3.length() + 1);
                        break;
                    case TIMESTAMP:
                    case DATETIME:
                        java.util.Date dt = rs.getDate(ci.getName());
                        if (dt == null) ps.setNull(++i, Types.TIMESTAMP);
                        else ps.setTimestamp(++i, new Timestamp(dt.getTime()));
                        break;
                    case DATE:
                        dt = rs.getDate(ci.getName());
                        if (dt == null) ps.setNull(++i, Types.DATE);
                        else ps.setDate(++i, new java.sql.Date(dt.getTime()));
                        break;
                    case TIME:
                        dt = rs.getDate(ci.getName());
                        if (dt == null) ps.setNull(++i, Types.TIME);
                        else ps.setTime(++i, new Time(dt.getTime()));
                        break;
                    default:
                        Object o = rs.get(ci.getName());
                        if (o == null) ps.setNull(++i, Types.VARCHAR);
                        else ps.setObject(++i, o);
                        break;
                }
            }
        } catch (SQLException e) {
            throw new JDiySqlException(e.getMessage(), e.getCause());
        }
    }

    private Pager<Rs> initLs(TableInfo tableInfo, ResultSet rs, int rowCount, int pageSize, int absPage) throws SQLException {
        ArrayList<Rs> al = new ArrayList<>();
        ResultSetMetaData rsmd = rs.getMetaData();
        int c = 0;

        while (rs.next() && (c++ < pageSize || pageSize == 0)) {
            Rs item = new Rs(tableInfo);
            for (int j = 1; j <= rsmd.getColumnCount(); j++) {
                item.put(rsmd.getColumnLabel(j), getColumnValue(rsmd, j, rs));
            }
            item.changedColumns.clear();
            item.isNew = false;
            al.add(item);
        }
        if (pageSize == 0) rowCount = al.size();
        Rs one = al.size() > 0 ? al.get(0) : new Rs(tableInfo);

        return new LsImpl(rowCount, pageSize, absPage, al, one);
    }

    protected Object getColumnValue(ResultSetMetaData rsmd, int sub, ResultSet rs) throws SQLException {
        int fieldType = rsmd.getColumnType((sub));
        Object val;
        switch (fieldType) {
            case Types.BOOLEAN:
            case Types.BIT:
                val = rs.getInt(sub);
                break;
            case Types.TINYINT:
            case Types.SMALLINT:
            case Types.INTEGER:
            case Types.BIGINT:
            case Types.FLOAT:
            case Types.REAL:
            case Types.DOUBLE:
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.LONGNVARCHAR:
            case Types.DATE:
            case Types.TIME:
            case Types.TIMESTAMP:
            case Types.NUMERIC:
            case Types.DECIMAL:
                val = rs.getObject(sub);
                break;
            case Types.CHAR:
            case Types.NCHAR://for mssql: remove empty string.
                Object obj;
                val = (obj = rs.getObject(sub)) == null ? null : obj.toString().trim();
                break;
            case Types.CLOB:
            case Types.NCLOB:
                val = clobToString(rs.getClob(sub));
                break;
            case Types.NULL:
                val = null;
                break;
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
            case Types.OTHER:
            case Types.JAVA_OBJECT:
            case Types.DISTINCT:
            case Types.STRUCT:
            case Types.ARRAY:
            case Types.BLOB:
            case Types.REF:
            case Types.DATALINK:
            case Types.ROWID:
            case Types.SQLXML:
            default:
                throw new JDiySqlException(Locale.DBG.getString("EE0018"),
                        String.valueOf(rsmd.getColumnType(sub)), rsmd.getTableName(sub),
                        rsmd.getColumnName(sub));
        }
        return val;
    }

    private static String clobToString(Clob clob) {
        if (clob == null) return null;
        try (Reader inStreamDoc = clob.getCharacterStream()) {
            char[] tempDoc = new char[(int) clob.length()];
            //noinspection ResultOfMethodCallIgnored
            inStreamDoc.read(tempDoc);
            return new String(tempDoc);
        } catch (Exception e) {
            throw new JDiyException(e.getMessage());
        }
    }

    private String getTableNameFromRSMD(ResultSetMetaData rsmd) throws SQLException {
        String tableName = "";
        int i = 1;
        while (StringUtils.isEmpty(tableName) && i++ < rsmd.getColumnCount()) {
            tableName = rsmd.getTableName(i);
        }
        return tableName;
    }

    private String getPageBreakSql(Args args, String filter, int pageStart) {
        //注： MySQL的pageStat是从０开始的（即第一条记录的下标为０）
        String where;
        if (!club.jdiy.utils.StringUtils.isEmpty(filter) && !"1=1".equals(filter.trim())) {
            String scan = (filter = filter.trim()).toUpperCase();
            if (scan.startsWith("1=1 ORDER ") || scan.startsWith("1=1 GROUP ")) where = filter.substring(3);
            else where = (scan.startsWith("ORDER ")) || scan.startsWith("GROUP ")
                    ? " " + filter
                    : " WHERE " + filter;
        } else {
            where = "";
        }
        StringBuilder sql = new StringBuilder("SELECT ")
                .append(args.getCols())
                .append(" FROM ")
                .append(args.getTable())
                .append(where);
        if (args.getPageSize() > 0) {
            sql.append(" LIMIT ")
                    .append(pageStart)
                    .append(", ")
                    .append(args.getPageSize());
        }
        return sql.toString();
    }

    @Resource
    private EntityManager em;
    @Resource
    private JDiyProperties jDiyProperties;
    @Resource
    AppContext context;
}
